package pms.common.dao;

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.sql.SQLException;

import javax.mail.Address;
import javax.mail.internet.AddressException;
import javax.mail.internet.InternetAddress;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import pms.common.value.QaEmailValue;
import pms.common.value.QaSearchValue;


public class QaListDaoImpl implements QaListDao {

	private JdbcTemplate jdbcTemplate;

	private QaEmailValue email = new QaEmailValue();

	public SqlRowSet getListData(QaSearchValue l_search) throws SQLException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException {
		// boolean l_flg = false;
		SqlRowSet l_rs = null;
		String l_sql = "select QA_NO,PROJECTID,TEMA,QA_LEVEL,QA_PART,QA_SUMMARY,QA_START_DATE,QA_FROM,QA_LIMIT_DATE,QA_STATUS,QA_KINKYUU,QA_TO "
				+ " from TBL_QA_LIST where QA_DEL_FLAG = '0' ";
		// プロジェクト
		if (!(l_search.getProjectId().equals("") || l_search.getProjectId()
				.equals(null))) {
			l_sql = l_sql + "and PROJECTID = '" + l_search.getProjectId()
					+ "' ";
			// l_flg = true;
		}
		// テーマ
		if (!(l_search.getTema().equals("") || l_search.getTema().equals(null))) {
			l_sql = l_sql + "and TEMA = '" + l_search.getTema() + "' ";
			// l_flg = true;
		}
		// 提出開始日
		if (!(l_search.getAskStartDate_Y().equals("") || l_search
				.getAskStartDate_Y().equals(null))
				&& !(l_search.getAskStartDate_M().equals("") || l_search
						.getAskStartDate_M().equals(null))
				&& !(l_search.getAskStartDate_D().equals("") || l_search
						.getAskStartDate_D().equals(null))) {
			l_sql = l_sql + "and QA_START_DATE >= '"
					+ l_search.getAskStartDate_Y()
					+ l_search.getAskStartDate_M()
					+ l_search.getAskStartDate_D() + "' ";
			// l_flg = true;
		}
		// 提出開始日
		if (!(l_search.getAskEndDate_Y().equals("") || l_search
				.getAskEndDate_Y().equals(null))
				&& !(l_search.getAskEndDate_M().equals("") || l_search
						.getAskEndDate_M().equals(null))
				&& !(l_search.getAskEndDate_D().equals("") || l_search
						.getAskEndDate_D().equals(null))) {
			l_sql = l_sql + "and QA_START_DATE <= '"
					+ l_search.getAskEndDate_Y() + l_search.getAskEndDate_M()
					+ l_search.getAskEndDate_D() + "' ";
			// l_flg = true;
		}
		// 提出者
		if (!(l_search.getAsker().equals("") || l_search.getAsker()
				.equals(null))) {
			l_sql = l_sql + "and QA_FROM = '" + l_search.getAsker() + "' ";
			// l_flg = true;
		}
		// 問合先
		if (!(l_search.getAsked().equals("") || l_search.getAsked()
				.equals(null))) {
			l_sql = l_sql + "and QA_TO = '" + l_search.getAsked() + "' ";
			// l_flg = true;
		}
		// 状態
		if (!(l_search.getSts().equals("") || l_search.getSts().equals(null))) {
			l_sql = l_sql + "and QA_STATUS = '" + l_search.getSts() + "' ";
			// l_flg = true;
		}
		l_sql = l_sql + "order by QA_START_DATE,PROJECTID,TEMA ";
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		return l_rs;
	}

	public boolean deleteData(String[] banGos) {
		boolean l_flg = false;
		String l_sql = "update TBL_QA_LIST set QA_DEL_FLAG = '1' where ";
		for (String l_banGo : banGos) {
			if (l_flg) {
				l_sql = l_sql + "or ";
			}
			l_sql = l_sql + "QA_NO = '" + l_banGo + "' ";
			l_flg = true;
		}
		int l_rs = jdbcTemplate.update(l_sql);
		if (l_rs < 0) {
			return false;
		} else {
			return true;
		}
	}

	public QaEmailValue getEmailData(String key) throws AddressException {

		email = new QaEmailValue();
		SqlRowSet l_rs = null;
		String l_sql = "select QA_NO,TEMA,QA_LEVEL,QA_PART,QA_SUMMARY,QA_QUESTION,QA_START_DATE,QA_FROM,QA_LIMIT_DATE,QA_STATUS,QA_KINKYUU,QA_TO "
				+ " from TBL_QA_LIST where QA_DEL_FLAG = '0' and QA_NO = '"
				+ key + "'";
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		while (l_rs.next()) {
			Address[] l_tmpAddress = {
					new InternetAddress("chen_yue@hyron.com"),
					new InternetAddress("yuzhangchun@hyron.com") };
			email.setSmtpServer("210.22.128.203");
			
			email.setMailFrom(new InternetAddress("yuzhangchun@hyron.com"));// TODO This E_mail mast read from TBL_USER
			email.setMailTo(new InternetAddress("yuzhangchun@hyron.com"));
			email.setCcAddress(l_tmpAddress);
			email.setMailSubject(l_rs.getString("QA_SUMMARY") + "について");
			String l_strMailBody = "TO:" + l_rs.getString("QA_TO") + "さん"
					+ "\r\n" + "CC:" + "XXX" + "さん" + "\r\n\r\n" + "お疲れ様です。"
					+ "余張春" + "です" + "\r\n\r\n" + "QA番号:" // TODO This userName mast read from TBL_USER
					+ "「"+l_rs.getString("QA_NO").trim()+ "」" + "\r\n" + "テーマ名:"
					+ "「"+l_rs.getString("TEMA")+ "」" + "\r\n" + "質問概要:"
					+ "「"+l_rs.getString("QA_SUMMARY")+ "」" + "\r\n" + "問題詳細:"
					+ "「"+l_rs.getString("QA_QUESTION")+ "」" + "\r\n" + "提出者:"
					+ "「"+l_rs.getString("QA_FROM")+ "」" + "\r\n" + "提出日付:"
					+ "「"+l_rs.getString("QA_START_DATE")+ "」" + "\r\n" + "回答期限:"
					+ "「"+l_rs.getString("QA_LIMIT_DATE")+ "」" + "\r\n" + "状態:"
					+ "「"+l_rs.getString("QA_STATUS")+ "」" + "\r\n" + "緊急度:"
					+ "「"+l_rs.getString("QA_KINKYUU")+ "」" + "\r\n\r\n"
					+ "以上、よろしくお願いします";
			email.setMailBody(l_strMailBody);
		}
		return email;
	}

	public InputStream expExcelData(String[] banGos) throws Exception {
		InputStream excelStream;
        StringBuffer excelBuf = new StringBuffer();
        excelBuf.append("項番\t");
        excelBuf.append("段階\t");
        excelBuf.append("ｻﾌﾞｼｽﾃﾑ\t");
        excelBuf.append("発生箇所\t");
        excelBuf.append("機能\t");
        excelBuf.append("質問概要\t");
        excelBuf.append("質問詳細\t");
        excelBuf.append("提出日付\t");
        excelBuf.append("提出者\t");
        excelBuf.append("回答期限\t");
        excelBuf.append("緊急度\t");
        excelBuf.append("問合先\t");
        excelBuf.append("回答内容\t");
        excelBuf.append("回答日付\t");
        excelBuf.append("確認者\t");
        excelBuf.append("確認日付\t");
        excelBuf.append("状態\t");
        excelBuf.append("備考\n");
        
        for (int a = 0; a < banGos.length; a++) {
			SqlRowSet l_value = getExpData(banGos[a]);
			while (l_value.next()) {
				excelBuf.append(l_value.getString("QA_NO")+"\t");
				excelBuf.append(l_value.getString("QA_LEVEL")+"\t");
				excelBuf.append(l_value.getString("PROJECTID")+"\t");
				excelBuf.append(l_value.getString("QA_PART")+"\t");
				excelBuf.append(l_value.getString("TEMA")+"\t");
				excelBuf.append("\"" + l_value.getString("QA_SUMMARY").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\t");
				excelBuf.append("\"" + l_value.getString("QA_QUESTION").replaceAll(
						"&lt;br&gt", "\r\n") + "\"" + "\t");
				excelBuf.append(l_value.getString("QA_START_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_FROM")+"\t");
				excelBuf.append(l_value.getString("QA_LIMIT_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_KINKYUU")+"\t");
				excelBuf.append(l_value.getString("QA_TO")+"\t");
				excelBuf.append("\"" + l_value.getString("QA_ANSWER").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\t");
				excelBuf.append(l_value.getString("QA_ANSWER_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_CONFIRMER")+"\t");
				excelBuf.append(l_value.getString("QA_CONFIRM_DATE")+"\t");
				excelBuf.append(l_value.getString("QA_STATUS")+"\t");
				excelBuf.append("\"" +l_value.getString("QA_MEMO").replaceAll("&lt;br&gt",
						"\r\n") + "\"" +"\n");
			}
		}
        String excelString = excelBuf.toString();
        excelStream = new ByteArrayInputStream(excelString.getBytes());
        return excelStream;
	}


	public SqlRowSet getExpData(String key) {
		SqlRowSet l_rs = null;
		String l_sql = "select QA_NO,PROJECTID,TEMA,QA_LEVEL,QA_PART,QA_SUMMARY,QA_QUESTION,QA_START_DATE,QA_FROM,QA_LIMIT_DATE,QA_KINKYUU,QA_TO,QA_ANSWER,QA_ANSWER_DATE,QA_CONFIRMER,QA_CONFIRM_DATE,QA_STATUS,QA_MEMO "
				+ " from TBL_QA_LIST where QA_DEL_FLAG = '0' and QA_NO = '"
				+ key + "'";
		l_rs = jdbcTemplate.queryForRowSet(l_sql);
		return l_rs;
	}


	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public QaEmailValue getEmail() {
		return email;
	}

	public void setEmail(QaEmailValue email) {
		this.email = email;
	}

}
